/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistencia;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author v3550
 */
public class LanceDAO {

    public LanceDAO() throws DAOException {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        } catch (ClassNotFoundException ex) {
            throw new DAOException("JdbcOdbDriver not found!!");
        }
    }

    private static Connection getConnection() throws SQLException {
        //derbyDB sera o nome do diretorio criado localmente
        return DriverManager.getConnection("jdbc:derby:derbyDB");
    }

    public LanceDTO gravar(LanceDTO c) throws DAOException {

        try {
            Connection con = getConnection();
            Statement sta = con.createStatement();
            String sql = "INSERT INTO ARTIGO_LANCE (ID_USUARIO, ID_ARTIGO, DATA, HORA, VALOR) VALUES ("
                    + c.getId_usuario() + ","
                    + c.getId_artigo() + ","
                    + "CURRENT_DATE ,CURRENT_TIME,"
                    + c.getLance() + ")";
            sta.executeUpdate(sql);

            sta.close();
            con.close();
            if (setCodigo(c)) {
                return c;
            }
        } catch (SQLException ex) {
            throw new DAOException(ex.getMessage());
        }
        return null;
    }

    private boolean setCodigo(LanceDTO p) throws DAOException {
        try {
            Connection con = getConnection();
            PreparedStatement stmt = con.prepareStatement(
                    " SELECT "
                    + "     A.ID_USUARIO, B.NOME, A.ID_ARTIGO, "
                    + "     A.DATA, A.HORA, A.VALOR  "
                    + " FROM "
                    + "     ARTIGO_LANCE A , USUARIO B"
                    + " WHERE "
                    + "     A.ID_USUARIO = B.ID "
                    + "     AND A.ID_USUARIO=? "
                    + "     AND A.ID_ARTIGO = ? "
                    + "     AND A.VALOR = ?");

            stmt.setInt(1, p.getId_usuario());
            stmt.setInt(2, p.getId_artigo());
            stmt.setDouble(3, p.getLance());

            ResultSet resultado = stmt.executeQuery();
            if (resultado.next()) {
                     p.setId_artigo(resultado.getInt("ID_ARTIGO"));
                     p.setId_usuario(resultado.getInt("ID_USUARIO"));
                     p.setLance(resultado.getDouble("VALOR"));
                     p.setDataLance(resultado.getDate("DATA"));
                     p.setHoraLance(resultado.getTime("HORA"));
                     p.setNomeDoUsuario(resultado.getString("NOME"));
                return true;
            }

        } catch (SQLException ex) {
            throw new DAOException("Falha ao adicionar. ->" + ex.getMessage());
        }
        return false;
    }

    public List<LanceDTO> getLances(ArtigoDTO l) throws DAOException {
        LanceDTO lance = null;
        List<LanceDTO> list = new LinkedList<LanceDTO>();

        try {
            Connection con = getConnection();
            Statement sta = con.createStatement();

            String sql = " SELECT "
                    + "     A.ID_USUARIO, B.NOME, A.ID_ARTIGO, "
                    + "     A.DATA, A.HORA, A.VALOR  "
                    + " FROM "
                    + "     ARTIGO_LANCE A , USUARIO B"
                    + " WHERE "
                    + "     A.ID_USUARIO = B.ID "
                    + "     AND A.ID_ARTIGO = " + l.getId();
                    
            ResultSet res = sta.executeQuery(sql);
           // System.out.println(""+ sql);
            while (res.next()) {
                lance = new LanceDTO();
               // lance.setId(res.getInt("ID"));
                lance.setId_artigo(res.getInt("ID_ARTIGO"));
                lance.setLance(res.getDouble("VALOR"));
                lance.setId_usuario(res.getInt("ID_USUARIO"));
                lance.setId_artigo(res.getInt("ID_ARTIGO"));
                lance.setId_usuario(res.getInt("ID_USUARIO"));
                lance.setLance(res.getDouble("VALOR"));
                lance.setDataLance(res.getDate("DATA"));
                lance.setHoraLance(res.getTime("HORA"));
                lance.setNomeDoUsuario(res.getString("NOME"));
                list.add(lance);
            }
            res.close();
            sta.close();
            con.close();
        } catch (Exception ex) {
            System.out.println(""+ ex);
            throw new DAOException(ex.getMessage());
        }

        return list;
    }

    public double getValorMínimo(ArtigoDTO a) throws DAOException {
        double valor = 0;
        try {
            Connection con = getConnection();
            Statement sta = con.createStatement();
            String sql = "SELECT MAX(VALOR) V FROM  ARTIGO_LANCE WHERE ID_ARTIGO = " + a.getId();
            ResultSet res = sta.executeQuery(sql);

            if (res.next()) {
                valor = res.getDouble("V");
            }

            sta.close();
            con.close();

        } catch (SQLException ex) {
            throw new DAOException(ex.getMessage());
        }
        return valor; 
        //return 0;
    }
    
    
       
}
